import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
import json


def validate_and_mark_errors(file_path):
    try:
        # 读取 Excel 文件
        df = pd.read_excel(file_path)
        error_messages = []

        # 加载 Excel 文件以修改样式
        wb = load_workbook(file_path)
        ws = wb.active
        red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

        # 定义列名映射
        columns = {
            "题目分类": "category",
            "题目内容": "content",
            "选项A": "optionA",
            "选项B": "optionB",
            "选项C": "optionC",
            "选项D": "optionD",
            "正确答案(只能是ABCD中的一个或是其排列组合)": "answer",
            "解析": "analysis",
            "题目难度(1-5)": "difficulty",
            "是否是多选(是或否)": "is_multiple"
        }

        # 检查列名是否匹配
        if not all(col in df.columns for col in columns.keys()):
            return {"status": "error", "message": "Excel 文件列名不匹配"}

        # 存储验证后的题目信息
        validated_questions = []

        # 遍历每一行数据
        for index, row in df.iterrows():
            row_errors = []
            row_num = index + 2  # Excel 行号从 2 开始

            # 检查题目难度
            difficulty = row["题目难度(1-5)"]
            if not (1 <= difficulty <= 5):
                row_errors.append(f"题目难度值 {difficulty} 无效（必须为 1-5）")
                ws.cell(row=row_num, column=df.columns.get_loc("题目难度(1-5)") + 1).fill = red_fill

            # 检查是否是多选
            is_multiple = row["是否是多选(是或否)"].strip().lower() == "是"
            answer = row["正确答案(只能是ABCD中的一个或是其排列组合)"].strip().upper()

            # 处理正确答案
            valid_chars = {'A', 'B', 'C', 'D'}
            answer_chars = set(answer)

            if is_multiple:
                # 多选：检查是否为合法字符组合且长度 >= 2
                if not answer_chars.issubset(valid_chars) or len(answer) < 2:
                    row_errors.append(f"多选答案 '{answer}' 无效（必须为至少两个选项）")
                    ws.cell(row=row_num,
                            column=df.columns.get_loc("正确答案(只能是ABCD中的一个或是其排列组合)") + 1).fill = red_fill
                else:
                    # 自动排序并去重（如 CA → AC）
                    sorted_answer = ''.join(sorted(answer_chars))
                    if sorted_answer != answer:
                        df.at[index, "正确答案(只能是ABCD中的一个或是其排列组合)"] = sorted_answer
            else:
                # 单选：检查是否为单个合法字符
                if len(answer) != 1 or answer not in valid_chars:
                    row_errors.append(f"单选答案 '{answer}' 无效（必须为 A、B、C 或 D）")
                    ws.cell(row=row_num,
                            column=df.columns.get_loc("正确答案(只能是ABCD中的一个或是其排列组合)") + 1).fill = red_fill

            # 收集错误信息
            if row_errors:
                error_messages.append(f"第 {row_num} 行错误: " + "; ".join(row_errors))
            else:
                # 将验证通过的行转换为题目对象
                question = {
                    "categoryName": row["题目分类"],
                    "cqContent": row["题目内容"],
                    "optionA": row["选项A"],
                    "optionB": row["选项B"],
                    "optionC": row["选项C"],
                    "optionD": row["选项D"],
                    "correctAnswer": row["正确答案(只能是ABCD中的一个或是其排列组合)"],
                    "analysis": row["解析"],
                    "difficulty": row["题目难度(1-5)"],
                    "isMultiple": is_multiple
                }
                validated_questions.append(question)

        # 保存标记后的 Excel 文件
        marked_file_path = file_path.replace(".xlsx", "_marked.xlsx")
        wb.save(marked_file_path)



        # 返回结果
        if error_messages:
            return {
                "status": "error",
                "message": "发现格式错误",
                "errors": error_messages,
                "marked_file": marked_file_path,
                "validated_questions": validated_questions  # 返回验证通过的题目
            }
        else:
            return {
                "status": "success",
                "message": "文件格式正确",
                "marked_file": file_path,  # 无错误时返回原文件
                "validated_questions": validated_questions  # 返回验证通过的题目
            }

    except Exception as e:
        return {"status": "error", "message": f"处理失败: {str(e)}"}





if __name__ == "__main__":
    file_path = "src/resources/.xlsx"  # 替换为你的 Excel 文件路径
    result = validate_and_mark_errors(file_path)
    print(result)  # 打印 JSON 结果